<?php
/**
 * Desc: Service模板
 * User: zdb
 * Date: 2023/6/5
 * Time: 11:22
 */

namespace app\api\service\develop;
use app\api\controller\develop\Table;
use app\api\service\SchemaService;
use think\facade\Db;
use think\facade\Env;

class TableService
{

    private $primaryTable;

    public function __construct()
    {
        $this->primaryTable = "yj_table";
    }

    public function findRowById($id)
    {
        return Db::table($this->primaryTable)->alias('a')
            ->where('id', '=', $id)
            ->strict(false)
            ->find();
    }

    public function findRowByData($data)
    {
        $condition = $this->assembleFilterCondition($data);
        return Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->strict(false)
            ->find();
    }

    public function getListByManyId($arrId)
    {
        $list = Db::table($this->primaryTable)->alias('a')
            ->whereIn('id', $arrId)
            ->select()
            ->toArray();
        return $list;
    }

    public function assembleFilterCondition($data)
    {
        $condition = ['a.status > 0'];

        if (!empty($data['table_name'])) {
            $condition[] = "table_name like '%" . $data['table_name'] . "%'";
        }

        if (!empty($data['example_id'])) {
            $condition[] = "example_id =".$data['example_id'];
        }

        return empty($condition) ? "" : implode(" AND ", $condition);
    }

    public function getListNoPage($data = [], $limitNum = 30)
    {
        $condition = $this->assembleFilterCondition($data);
        $res = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->limit(0, $limitNum)
            ->order('a.id desc')
            ->select()
            ->toArray();
        return $res;
    }

    public function getListWithPage($data, $pageIndex = 1, $pageSize = 30)
    {
        $offset = ($pageIndex - 1) * $pageSize;
        $condition = $this->assembleFilterCondition($data);
        $res = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->limit($offset, $pageSize)
            ->order('a.id desc')
            ->select();
        return $res;
    }

    public function getTotalNum($data)
    {
        $condition = $this->assembleFilterCondition($data);
        $num = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->count();
        return $num;
    }

    public function createRow($data)
    {
        $data['status'] = isset($data['status']) ? $data['status']:1;
        $res = Db::table($this->primaryTable)
            ->duplicate(array('table_name'))
            ->strict(false)
            ->insert($data, true);
        return $res;
    }

    public function createManyRow($data)
    {
        $res = Db::table($this->primaryTable)
            ->strict(false)
            ->duplicate($data)
            ->insertAll($data, true);
        return $res;
    }

    public function updateRowById($id, $data)
    {
        $res = Db::table($this->primaryTable)->alias('a')
            ->where('id', '=', $id)
            ->strict(false)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function updateListByData($condition, $data)
    {
        $condition = $this->assembleFilterCondition($data);
        $res = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function updateListByManyId($arrId, $data)
    {
        $res = Db::table($this->primaryTable)->alias('a')
            ->whereIn('a.id', $arrId)
            ->strict(false)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function removeRowById($id)
    {
        $data = ['status' => 0];
        $res = Db::table($this->primaryTable)
            ->where('id', '=', $id)
            ->strict(false)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function removeListByManyId($arrId)
    {
        $data = ['status' => 0];
        $res = Db::table($this->primaryTable)->alias('a')
            ->whereIn('a.id', $arrId)
            ->strict(false)
            ->save($data);
        return false !== $res ? true : false;
    }

    public function removeListByData($data)
    {
        $condition = $this->assembleFilterCondition($data);
        $data = ['status' => 0];
        $res = Db::table($this->primaryTable)->alias('a')
            ->where($condition)
            ->save($data);
        return false !== $res ? true : false;
    }

    /**
     * Desc:
     * Datetime: 2024/11/29 8:11 by zdb
     */
    public function synchronizeTable()
    {
        $dbName = Env::get("DATABASE_PRIMARY.DB_NAME");
        $schemaService = new SchemaService();
        $dbTableList = $schemaService->getTableList($dbName);
        $nowTableList = $this->getListNoPage(array(), 300);

        $columnOriginalTable = array_column($dbTableList, 'table_name');
        $columnNowTable = array_column($nowTableList, 'table_name');

        $arrNewAddTable = array_diff($columnOriginalTable, $columnNowTable);
        if(empty($arrNewAddTable)){
            return array('flag' => 2, 'message' => "已经是最新，无需再同步");   //已经是最新，无需再同步
        }
        $columnOriginalDataByTable = array_column($dbTableList, null, 'table_name');

        $arrSortByFieldName = array(
            'status' => 97,
            'create_time' => 98,
            'update_time' => 99
        );
        $tableFieldService = new TableFieldService();
        Db::table($this->primaryTable)->startTrans();
        try{
            foreach($arrNewAddTable as $newTableName){
                $addData = array(
                    'table_name' => $newTableName,
                    'comment' => $columnOriginalDataByTable[$newTableName]["comment"] ?: ""
                );
                $newTableId = $this->createRow($addData);

                $fieldList = $schemaService->getOriginalTableFieldList($newTableName);
                foreach($fieldList as $fieldItem){
                    $addFieldData = [
                        'table_id'   => $newTableId,
                        'table_name' => $fieldItem['TABLE_NAME'],
                        'field_name' => $fieldItem['COLUMN_NAME'],
                        'field_title' => $fieldItem['COLUMN_COMMENT'],
                        'field_comment' => $fieldItem['COLUMN_COMMENT'],
                        'data_type' => $fieldItem['DATA_TYPE'],
                        'sort' => $arrSortByFieldName[$fieldItem['COLUMN_NAME']] ?? 0
                    ];
                    $tableFieldService->createRow($addFieldData);
                }
            }
            Db::table($this->primaryTable)->commit();
            return array('flag' => 1, 'message' => "");
        }catch(\Exception $e){
            Db::table($this->primaryTable)->rollback();
            return array('flag' => 0, 'message' => $e->getMessage());
        }
    }
}